import logging
from datetime import date
from typing import List

from fastapi import APIRouter, Query, Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

mrk_head_router = APIRouter(prefix="/issue/mrkHead", tags=["设备报废管理"], dependencies=[Depends(oauth2_scheme)])


class MrkSearch(PaginationBase):
    fsiteNo: str = Field(None, description="")
    ftakedateStart: str = Field(None, description="")
    ftakedateEnd: str = Field(None, description="")
    fpointNo: str = Field(None, description="")
    fbillno: str = Field(None, description="")
    fmrkuser: str = Field(None, description="")
    fbillstate: str = Field(None, description="")


@mrk_head_router.get("/list", summary="查询设备保费信息")
def list_mrk_head(mrk: MrkSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_mrk_head where 1=1"
    if mrk.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = mrk.fsiteNo
    if mrk.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param['fpointNo'] = mrk.fpointNo
    if mrk.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = mrk.fbillno
    if mrk.fmrkuser is not None:
        sql = sql + " and fmrkuser = :fmrkuser "
        param['fmrkuser'] = mrk.fmrkuser
    if mrk.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = mrk.fbillstate
    if (mrk.ftakedateStart is not None) and (mrk.ftakedateEnd is not None):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = mrk.ftakedateStart
        param['ftakedateEnd'] = mrk.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmrkuser,fmrkdate,fpoint_no,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.s_tc_mrk_head where 1=1"
    if mrk.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = mrk.fsiteNo
    if mrk.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param['fpointNo'] = mrk.fpointNo
    if mrk.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = mrk.fbillno
    if mrk.fmrkuser is not None:
        sql = sql + " and fmrkuser = :fmrkuser "
        param['fmrkuser'] = mrk.fmrkuser
    if mrk.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = mrk.fbillstate
    if (mrk.ftakedateStart is not None) and (mrk.ftakedateEnd is not None):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = mrk.ftakedateStart
        param['ftakedateEnd'] = mrk.ftakedateEnd
    if mrk.order == "desc":
        sql = sql + " order by  fmrkdate,fbillno desc "
    else:
        sql = sql + " order by  fmrkdate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param['limit'] = mrk.limit
    param['offset'] = mrk.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpoint_size,b.fequipmentbarcode,b.feuipmentid,b.fmacno,"
    sql_3 = sql_3 + " b.fequipmenttag,b.flinkmac,b.flinkmark,b.fbuyprice"
    sql_3 = sql_3 + " from lims.s_tc_mrk_head a,lims.s_tc_mrk_detail b where a.fbillno=b.fbillno "
    if mrk.fsiteNo is not None:
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param['site_no'] = mrk.fsiteNo
    if mrk.fpointNo is not None:
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param['fpointNo'] = mrk.fpointNo
    if mrk.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = mrk.fbillno
    if mrk.fmrkuser is not None:
        sql_3 = sql_3 + " and fmrkuser = :fmrkuser "
        param['fmrkuser'] = mrk.fmrkuser
    if mrk.fbillstate is not None:
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param['fbillstate'] = mrk.fbillstate
    if (mrk.ftakedateStart is not None) and (mrk.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = mrk.ftakedateStart
        param['ftakedateEnd'] = mrk.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmrkuser'] = data[key][2]
        d1['fmrkdate'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpointsize'] = data_detail[j1][2]
                d2['fequipmentbarcode'] = data_detail[j1][3]
                d2['feuipmentid'] = data_detail[j1][4]
                d2['fmacno'] = data_detail[j1][5]
                d2['fequipmenttag'] = data_detail[j1][6]
                d2['flinkmac'] = data_detail[j1][7]
                d2['flinkmark'] = data_detail[j1][8]
                d2['fbuyprice'] = data_detail[j1][9]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": mrk.limit, "page": mrk.pageIndex,
                 "pages": (count + mrk.limit - 1) // mrk.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TcMrkDetailCreate(BaseModel):
    """设备报废明细创建模型"""
    fline: int = Field(..., description="行号")
    fpointSize: str = Field("", description="点位规格", max_length=50)
    fequipmentbarcode: str = Field(..., description="设备条码", max_length=100)
    feuipmentid: int = Field(..., description="设备ID")
    fmacno: str = Field("", description="MAC地址", max_length=50)
    fequipmenttag: str = Field("", description="设备标签", max_length=100)
    fbuyprice: float = Field(0.0, description="采购价格")


class TcMrkHeaderCreate(BaseModel):
    """设备报废表头创建模型"""
    fbillno: str = Field(..., description="报废单号", max_length=30)
    fsiteNo: str = Field(..., description="站点编号", max_length=20)
    fpointNo: str = Field(..., description="点位编号", max_length=50)
    fmrkuser: str = Field(..., description="报废人", max_length=20)
    fmrkdate: date = Field(..., description="报废日期")
    foperator: str = Field(..., description="操作员", max_length=20)
    fupddate: date = Field(..., description="更新日期")
    fbillstate: int = Field(1, description="单据状态，默认1")
    fexplanation: str = Field("", description="报废说明", max_length=200)
    details: List[TcMrkDetailCreate] = Field(..., description="报废明细列表")


@mrk_head_router.post("/create", summary="新增设备报废信息")
def create_mrk_head(tc: TcMrkHeaderCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = tc.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = tc.details
            for data in jsondata:
                sql = "insert into lims.s_tc_mrk_detail(fbillno,fline,fpoint_size,fequipmentbarcode,feuipmentid,fmacno,fequipmenttag,fbuyprice)"
                sql = sql + " values( :fbillno , :fline , :fpointSize , :fequipmentbarcode , :feuipmentid , :fmacno , :fequipmenttag , :fbuyprice )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpointSize": data.fpointSize,
                    "fequipmentbarcode": data.fequipmentbarcode,
                    "feuipmentid": data.feuipmentid,
                    "fmacno": data.fmacno,
                    "fequipmenttag": data.fequipmenttag,
                    "fbuyprice": data.fbuyprice
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + 1
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data['fline']
                d2['fequipmentbarcode'] = data['fequipmentbarcode']
                d2['fequipmenttag'] = data['fequipmenttag']
                d2['feuipmentid'] = data['feuipmentid']
                d2['fbuyprice'] = float(data['fbuyprice'])
                d2['flinkmac'] = None
                d2['flinkmark'] = None
                d2['fmacno'] = data['fmacno']
                d2['fpointSize'] = data['fpointSize']
                detail_list.append(d2)
            sql = "insert into lims.s_tc_mrk_head(fbillno,fsite_no,fpoint_no,fmrkuser,fmrkdate,fitemcount,fpiececount,fexplanation,fupddate,foperator,fbilltype,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :fpointNo , :fmrkuser , :fmrkdate , :fitemcount , :fpiececount , :fexplanation , :fupddate , :foperator ,1, :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": tc.fsiteNo,
                "fpointNo": tc.fpointNo,
                "fmrkuser": tc.fmrkuser,
                "fmrkdate": tc.fmrkdate,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "fexplanation": tc.fexplanation,
                "fupddate": tc.fupddate,
                "foperator": tc.foperator,
                "fbillstate": tc.fbillstate
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = tc.fsiteNo
            d1['fmrkdate'] = tc.fmrkdate
            d1['fmrkuser'] = tc.fmrkuser
            d1['fpointNo'] = tc.fpointNo
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftakedateEnd'] = None
            d1['ftakedateStart'] = None
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = tc.fexplanation
            d1['fupddate'] = tc.fupddate
            d1['foperator'] = tc.foperator
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['ftotalamt'] = None
            d1['fbilltype'] = 1
            d1['fbillstate'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mrk_head_router.post("/update", summary="修改报废设置")
def update_mrk_head(tc: TcMrkHeaderCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        user_id = EncryptionMiddleware.get_current_user(token)
        fbillstate = tc.fbillstate
        fbillno = tc.fbillno
        with db.begin():
            if (fbillstate == 8):
                print("作废")
                sql = "update lims.s_tc_mrk_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": tc.fbillno,
                    "userid": user_id,
                })
                return BaseResponse.success()
            else:
                sql = "delete from lims.s_tc_mrk_detail where fbillno= :fbillno "
                db.execute(text(sql), {"fbillno": fbillno})

                jsondata = tc.details
                for data in jsondata:
                    sql = "insert into lims.s_tc_mrk_detail(fbillno,fline,fpoint_size,fequipmentbarcode,feuipmentid,fmacno,fequipmenttag,fbuyprice)"
                    sql = sql + " values( :fbillno , :fline , :fpointSize , :fequipmentbarcode , :feuipmentid , :fmacno , :fequipmenttag , :fbuyprice )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "fpointSize": data.fpointSize,
                        "fequipmentbarcode": data.fequipmentbarcode,
                        "feuipmentid": data.feuipmentid,
                        "fmacno": data.fmacno,
                        "fequipmenttag": data.fequipmenttag,
                        "fbuyprice": data.fbuyprice
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = fpiececount + 1
                sql = "update lims.s_tc_mrk_head "
                sql = sql + " set fmrkdate= :fmrkdate ,fmrkuser= :fmrkuser ,fexplanation= :fexplanation "
                sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount "
                sql = sql + ",fupddate=now(),foperator= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fmrkdate": tc.fmrkdate,
                    "fmrkuser": tc.fmrkuser,
                    "fexplanation": tc.fexplanation,
                    "fitemcount": fitemcount,
                    "fpiececount": fpiececount,
                    "userid": user_id,
                    "fbillno": fbillno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
